# Copyright (c) 2019, Frappe Technologies and contributors
# For license information, please see license.txt


import csv
import io
import json
import re
from datetime import date, datetime

import frappe
import mt940
import openpyxl
from frappe import _
from frappe.core.doctype.data_import.data_import import DataImport
from frappe.core.doctype.data_import.importer import Importer, ImportFile
from frappe.utils.background_jobs import enqueue
from frappe.utils.file_manager import get_file, save_file
from frappe.utils.xlsxutils import ILLEGAL_CHARACTERS_RE, handle_html
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter
from rapidfuzz import fuzz, process
from rapidfuzz.utils import default_process

# 导入付款凭证相关工具
from erpnext.accounts.doctype.payment_entry.payment_entry import get_payment_entry

INVALID_VALUES = ("", None)


# 自动匹配相关类
class AutoMatchParty:
    """
    Matches by Account/IBAN and then by Party Name/Description sequentially.
    Returns when a result is obtained.

    Result (if present) is of the form: (Party Type, Party,)
    """

    def __init__(self, **kwargs) -> None:
        self.__dict__.update(kwargs)
        self.auto_create_payment_entry = getattr(self, "auto_create_payment_entry", True)  # 默认启用
        self.payment_submit_option = getattr(self, "payment_submit_option", "Save as Draft")  # 默认保存为草稿
        # 添加汇率默认值（从公司设置获取）
        self.company_default_currency = frappe.get_cached_value("Company", self.company, "default_currency") if self.company else "USD"

    def get(self, key):
        return self.__dict__.get(key, None)

    def match(self) -> tuple | None:
        result = None
        result = AutoMatchbyAccountIBAN(
            bank_party_account_number=self.bank_party_account_number,
            bank_party_iban=self.bank_party_iban,
            deposit=self.deposit,
        ).match()

        fuzzy_matching_enabled = frappe.get_single_value("Accounts Settings", "enable_fuzzy_matching")
        if not result and fuzzy_matching_enabled:
            result = AutoMatchbyPartyNameDescription(
                bank_party_name=self.bank_party_name, description=self.description, deposit=self.deposit
            ).match()

        return result


class AutoMatchbyAccountIBAN:
    def __init__(self,** kwargs) -> None:
        self.__dict__.update(kwargs)

    def get(self, key):
        return self.__dict__.get(key, None)

    def match(self):
        if not (self.bank_party_account_number or self.bank_party_iban):
            return None

        return self.match_account_in_party()

    def match_account_in_party(self) -> tuple | None:
        """
        Returns (Party Type, Party) if a matching account is found in Bank Account or Employee:
        1. Get party from a matching (iban/account no) Bank Account
        2. If not found, get party from Employee with matching bank account details (iban/account no)
        """
        if not (self.bank_party_account_number or self.bank_party_iban):
            # Nothing to match
            return None

        # Search for a matching Bank Account that has party set
        party_result = frappe.db.get_all(
            "Bank Account",
            or_filters=self.get_or_filters(),
            filters={"party_type": ("is", "set"), "party": ("is", "set")},
            fields=["party", "party_type"],
            limit_page_length=1,
        )
        if result := party_result[0] if party_result else None:
            return (result["party_type"], result["party"])

        # If no party is found, search in Employee (since it has bank account details)
        if employee_result := frappe.db.get_all(
            "Employee", or_filters=self.get_or_filters("Employee"), pluck="name", limit_page_length=1
        ):
            return ("Employee", employee_result[0])

    def get_or_filters(self, party: str | None = None) -> dict:
        """Return OR filters for Bank Account and IBAN"""
        or_filters = {}
        if self.bank_party_account_number:
            bank_ac_field = "bank_ac_no" if party == "Employee" else "bank_account_no"
            or_filters[bank_ac_field] = self.bank_party_account_number

        if self.bank_party_iban:
            or_filters["iban"] = self.bank_party_iban

        return or_filters


class AutoMatchbyPartyNameDescription:
    def __init__(self, **kwargs) -> None:
        self.__dict__.update(kwargs)

    def get(self, key):
        return self.__dict__.get(key, None)

    def match(self) -> tuple | None:
        # fuzzy search by customer/supplier & employee
        if not (self.bank_party_name or self.description):
            return None

        return self.match_party_name_desc_in_party()

    def match_party_name_desc_in_party(self) -> tuple | None:
        """Fuzzy search party name and/or description against parties in the system"""
        result = None
        parties = get_parties_in_order(self.deposit)

        for party in parties:
            filters = {"status": "Active"} if party == "Employee" else {"disabled": 0}
            field = f"{party.lower()}_name"
            names = frappe.get_all(party, filters=filters, fields=[f"{field} as party_name", "name"])

            for field in ["bank_party_name", "description"]:
                if not self.get(field):
                    continue

                result, skip = self.fuzzy_search_and_return_result(party, names, field)
                if result or skip:
                    break

            if result or skip:
                # Skip If: It was hard to distinguish between close matches and so match is None
                # OR if the right match was found
                break

        return result

    def fuzzy_search_and_return_result(self, party, names, field) -> tuple | None:
        skip = False
        result = process.extract(
            query=self.get(field),
            choices={row.get("name"): row.get("party_name") for row in names},
            scorer=fuzz.token_set_ratio,
            processor=default_process,
        )
        party_name, skip = self.process_fuzzy_result(result)

        return ((party, party_name), skip) if party_name else (None, skip)

    def process_fuzzy_result(self, result: list | None):
        """
        If there are multiple valid close matches return None as result may be faulty.
        Return the result only if one accurate match stands out.

        Returns: Result, Skip (whether or not to discontinue matching)
        """
        SCORE, PARTY_ID, CUTOFF = 1, 2, 80

        if not result or not len(result):
            return None, False

        first_result = result[0]
        if len(result) == 1:
            return (first_result[PARTY_ID] if first_result[SCORE] > CUTOFF else None), True

        if first_result[SCORE] > CUTOFF:
            second_result = result[1]
            # If multiple matches with the same score, return None but discontinue matching
            # Matches were found but were too close to distinguish between
            if first_result[SCORE] == second_result[SCORE]:
                return None, True

            return first_result[PARTY_ID], True
        else:
            return None, False


def get_parties_in_order(deposit: float) -> list:
    return (
        ["Customer", "Supplier", "Employee"]  # most -> least likely to pay us
        if frappe.utils.flt(deposit) > 0
        else ["Supplier", "Employee", "Customer"]  # most -> least likely to receive from us
    )


# 银行对账单导入主类
class BankStatementImportCN(DataImport):
    # begin: auto-generated types
    from typing import TYPE_CHECKING

    if TYPE_CHECKING:
        from frappe.types import DF

        bank: DF.Link | None
        bank_account: DF.Link
        company: DF.Link
        custom_delimiters: DF.Check
        delimiter_options: DF.Data | None
        google_sheets_url: DF.Data | None
        import_file: DF.Attach | None
        import_mt940_fromat: DF.Check
        import_type: DF.Literal["", "Insert New Records", "Update Existing Records"]
        mute_emails: DF.Check
        reference_doctype: DF.Link
        show_failed_logs: DF.Check
        status: DF.Literal["Pending", "Success", "Partial Success", "Error"]
        submit_after_import: DF.Check
        template_options: DF.Code | None
        template_warnings: DF.Code | None
        use_csv_sniffer: DF.Check
        auto_create_payment_entry: DF.Check
        payment_submit_option: DF.Literal["Save as Draft", "Submit Automatically"]
    # end: auto-generated types 

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.auto_create_payment_entry = getattr(self, "auto_create_payment_entry", True)

    def validate(self):
        doc_before_save = self.get_doc_before_save()
        if (
            not (self.import_file or self.google_sheets_url)
            or (doc_before_save and doc_before_save.import_file != self.import_file)
            or (doc_before_save and doc_before_save.google_sheets_url != self.google_sheets_url)
        ):
            template_options_dict = {}
            column_to_field_map = {}
            bank = frappe.get_doc("Bank", self.bank)
            for i in bank.bank_transaction_mapping:
                column_to_field_map[i.file_field] = i.bank_transaction_field
            template_options_dict["column_to_field_map"] = column_to_field_map
            self.template_options = json.dumps(template_options_dict)
            self.template_warnings = ""

        if self.import_file and not self.import_file.lower().endswith(".txt"):
            self.validate_import_file()
            self.validate_google_sheets_url()

        # 验证付款账户配置
        if self.auto_create_payment_entry:
            bank_account = frappe.get_doc("Bank Account", self.bank_account)
            if not bank_account.account:
                frappe.throw(_("Bank Account {0} must be linked to a G/L Account").format(self.bank_account))

    def start_import(self):
        preview = frappe.get_doc("Bank Statement Import", self.name).get_preview_from_template(
            self.import_file, self.google_sheets_url
        )

        if "Bank Account" not in json.dumps(preview["columns"]):
            frappe.throw(_("Please add the Bank Account column"))

        from frappe.utils.background_jobs import is_job_enqueued
        from frappe.utils.scheduler import is_scheduler_inactive

        run_now = frappe.in_test or frappe.conf.developer_mode
        if is_scheduler_inactive() and not run_now:
            frappe.throw(_("Scheduler is inactive. Cannot import data."), title=_("Scheduler Inactive"))

        job_id = f"bank_statement_import::{self.name}"
        if not is_job_enqueued(job_id):
            enqueue(
                start_import,
                queue="default",
                timeout=6000,
                event="data_import",
                job_id=job_id,
                data_import=self.name,
                bank_account=self.bank_account,
                import_file_path=self.import_file,
                google_sheets_url=self.google_sheets_url,
                bank=self.bank,
                template_options=self.template_options,
                auto_create_payment_entry=self.auto_create_payment_entry,
                payment_submit_option=self.payment_submit_option,
                now=run_now,
            )
            return job_id

        return None

    def create_payment_entries(self, bank_transactions):
        """根据银行交易记录生成付款凭证（完整字段映射）"""
        payment_entries = []
        bank_account_doc = frappe.get_doc("Bank Account", self.bank_account)
        company_doc = frappe.get_doc("Company", self.company)
        
        for idx, transaction in enumerate(bank_transactions):
            # 只处理支出交易且未创建付款凭证
            if transaction.transaction_type != "Outward" or transaction.is_auto_payment_created:
                continue

            try:
                # 1. 从银行交易记录提取关键信息（增强字段映射）
                amount = transaction.withdrawal or transaction.amount
                transaction_date = transaction.transaction_date or transaction.date
                payee = transaction.party or transaction.beneficiary or transaction.bank_party_name
                reference_number = transaction.reference_number or transaction.cheque_number
                
                if not amount or amount <= 0:
                    frappe.log_error(
                        title="Payment Entry Creation Failed",
                        message=f"Invalid amount for transaction {transaction.name}"
                    )
                    continue

                # 2. 使用自动匹配系统匹配交易方
                party_type, party = self.auto_match_party(transaction)
                
                # 如果没有匹配到交易方，尝试只匹配供应商
                if not party:
                    party = self.get_supplier_from_transaction(transaction)
                    party_type = "Supplier" if party else None
                
                if not party:
                    frappe.log_error(
                        title="Payment Entry Creation Failed",
                        message=f"No party found for transaction {transaction.name} (Description: {transaction.description})"
                    )
                    continue

                # 3. 创建基础付款凭证
                pe = get_payment_entry(
                    party_type, 
                    party,
                    bank_account=self.bank_account,
                    amount=amount,
                    reference_date=transaction_date,
                    reference_no=reference_number
                )

                # 4. 完善付款凭证字段（基于Payment Entry字段规范）
                pe.update({
                    # 基础信息
                    "company": self.company,
                    "payment_type": "Pay",  # 明确为付款类型
                    "posting_date": transaction_date or date.today(),
                    "mode_of_payment": transaction.payment_method or self.get_mode_of_payment(transaction.description),
                    
                    # 账户信息
                    "paid_from": bank_account_doc.account,
                    "paid_from_account_currency": bank_account_doc.currency,
                    "paid_to_account_currency":self.get_party_currency(party_type, party),
                    "paid_from_account_type": bank_account_doc.account_type,  # 补充账户类型
                    "paid_to": self.get_party_account(party_type, party),  # 交易方的会计科目
                    "paid_to_account_type": "Receivable" if party_type == "Customer" else "Payable",
                
                    # 金额信息
                    "paid_amount": amount,
                    "base_paid_amount": amount * self.get_exchange_rate(bank_account_doc.currency, company_doc.default_currency),
                    "received_amount": 0,
                    "base_received_amount": 0,
                    "source_exchange_rate": self.get_exchange_rate(bank_account_doc.currency, company_doc.default_currency),
                    "target_exchange_rate": 1.0,
                
                    # 参考信息
                    "reference_no": reference_number,
                    "reference_date": transaction_date,
                    "remarks": f"Auto-created from bank transaction {transaction.name}: {transaction.description}",
                    "references": self.get_payment_references(transaction, party_type, party),  # 关联发票
                    "total_allocated_amount": amount,
                    "base_total_allocated_amount": amount * self.get_exchange_rate(bank_account_doc.currency, company_doc.default_currency),
                
                    # 关联信息
                    "bank_statement_import": self.name,
                    "party_bank_account": self.get_party_bank_account(party_type, party),
                    "remarks": f"自动创建自银行交易 {transaction.name}: {transaction.description}",
                    "status": "Draft" if self.payment_submit_option == "Save as Draft" else "Submitted"
                })

                # 5. 处理税费（对应taxes_and_charges_section）
                if party_type == "Supplier":
                    pe.update({
                    "purchase_taxes_and_charges_template": self.get_default_tax_template(party),
                    "taxes": self.calculate_taxes(amount, party)  # 自动计算税费
                })

                # 6. 保存并提交（根据配置）
                pe.insert(ignore_permissions=True)
                
                if self.payment_submit_option == "Submit Automatically":
                    pe.submit()

                # 7. 更新银行交易记录关联
                self.update_bank_transaction(transaction, pe)

                payment_entries.append(pe.name)
                
                # 发送进度更新
                frappe.publish_realtime(
                    "payment_entry_progress",
                    {
                        "data_import": self.name,
                        "current": idx + 1,
                        "total": len(bank_transactions)
                    },
                    user=frappe.session.user
                )

            except Exception as e:
                frappe.log_error(
                    title="Payment Entry Creation Failed",
                    message=f"Error creating payment entry for transaction {transaction.name}: {str(e)}"
                )

        return payment_entries

    def get_party_account(self, party_type, party):
        """获取交易方对应的会计科目（如供应商的应付账款科目）"""
        if party_type == "Supplier":
            return frappe.get_cached_value("Supplier", party, "default_payable_account")
        elif party_type == "Customer":
            return frappe.get_cached_value("Customer", party, "default_receivable_account")
        return ""

def get_exchange_rate(self, source_currency, target_currency):
    """获取汇率（用于金额换算）"""
    if source_currency == target_currency:
        return 1.0
    return frappe.get_all("Currency Exchange", 
        filters={"from_currency": source_currency, "to_currency": target_currency},
        fields=["exchange_rate"], limit=1)[0].exchange_rate or 1.0

def get_payment_references(self, transaction, party_type, party):
    """关联发票或订单（对应references字段）"""
    references = []
    if party_type == "Supplier" and transaction.reference_number:
        # 匹配采购发票
        invoices = frappe.get_all("Purchase Invoice", 
            filters={"supplier": party, "supplier_invoice_number": transaction.reference_number},
            fields=["name", "grand_total"])
        for inv in invoices:
            references.append({
                "reference_doctype": "Purchase Invoice",
                "reference_name": inv.name,
                "allocated_amount": min(transaction.amount, inv.grand_total)
            })
    return references

    def update_bank_transaction(self, transaction, payment_entry):
        """更新银行交易记录，标记已创建付款凭证并建立关联"""
        # 更新银行交易主记录
        transaction.payment_entry = payment_entry.name
        transaction.is_auto_payment_created = True
        transaction.save(ignore_permissions=True)
        frappe.db.set_value("Payment Entry", payment_entry.name, "bank_transaction", transaction.name)
        # 创建关联记录（BankTransactionPayments）
        frappe.get_doc({
            "doctype": "BankTransactionPayments",
            "parent": transaction.name,
            "parenttype": "Bank Transaction",
            "parentfield": "payments",
            "payment_document": "Payment Entry",
            "payment_entry": payment_entry.name,
            "allocated_amount": payment_entry.paid_amount,
            "clearance_date": transaction.clearance_date,
        }).insert(ignore_permissions=True)

def auto_match_party(self, transaction):
    """增强的自动匹配：优先匹配账户，再模糊匹配名称，最后匹配历史交易"""
    # 1. 优先通过银行账户/IBAN匹配（精确匹配）
    match_info = {
        "bank_party_account_number": transaction.bank_account_number,
        "bank_party_iban": transaction.iban,
        "bank_party_name": transaction.bank_party_name,
        "description": transaction.description,
        "deposit": transaction.deposit
    }
    matcher = AutoMatchParty(** match_info)
    party_type, party = matcher.match() or (None, None)
    
    # 2. 如果未匹配到，查询历史交易记录
    if not party:
        history = frappe.get_all("Payment Entry",
            filters={"party_name": ("like", f"%{transaction.bank_party_name}%")},
            fields=["party_type", "party"], limit=1)
        if history:
            party_type, party = history[0].party_type, history[0].party
    
    return party_type, party

    def get_supplier_from_transaction(self, transaction):
        """从交易记录中匹配供应商（增强逻辑）"""
        # 1. 优先从明确的供应商字段匹配
        if hasattr(transaction, "supplier") and transaction.supplier:
            return transaction.supplier

        # 2. 从描述中提取供应商名称匹配
        if transaction.description:
            # 清洗特殊字符
            clean_desc = re.sub(r'[^\w\s]', '', transaction.description)
            # 尝试匹配供应商名称
            suppliers = frappe.get_all(
                "Supplier",
                filters={"supplier_name": ["like", f"%{clean_desc}%"]},
                limit=1
            )
            if suppliers:
                return suppliers[0].name

        # 3. 从参考号匹配供应商税号
        if transaction.reference_number:
            suppliers = frappe.get_all(
                "Supplier",
                filters={"tax_id": ["like", f"%{transaction.reference_number}%"]},
                limit=1
            )
            if suppliers:
                return suppliers[0].name

        return None

    def get_mode_of_payment(self, description):
        """从描述中推断付款方式"""
        mode_mapping = {
            "TRANSFER": "Bank Transfer",
            "CHEQUE": "Cheque",
            "CARD": "Credit Card",
            "WIRE": "Wire Transfer"
        }
        for keyword, mode in mode_mapping.items():
            if keyword in description.upper():
                return mode
        return "Bank Transfer"  # 默认值

    def get_party_bank_account(self, party_type, party):
        """获取交易方默认银行账户"""
        party_bank = frappe.get_all(
            "Party Bank Account",
            filters={"party_type": party_type, "party": party, "is_default": 1},
            fields=["name"],
            limit=1
        )
        return party_bank[0].name if party_bank else None

    def get_purchase_invoice(self, transaction):
        """尝试匹配采购发票"""
        if transaction.reference_number:
            inv = frappe.get_all(
                "Purchase Invoice",
                filters={"supplier_invoice_number": transaction.reference_number},
                fields=["name"],
                limit=1
            )
            return inv[0].name if inv else None
        return None


@frappe.whitelist()
def convert_mt940_to_csv(data_import, mt940_file_path):
    doc = frappe.get_doc("Bank Statement Import", data_import)

    file_doc, content = get_file(mt940_file_path)

    if not is_mt940_format(content):
        frappe.throw(_("The uploaded file does not appear to be in valid MT940 format."))

    if is_mt940_format(content) and not doc.import_mt940_fromat:
        frappe.throw(_("MT940 file detected. Please enable 'Import MT940 Format' to proceed."))

    try:
        transactions = mt940.parse(content)
    except Exception as e:
        frappe.throw(_("Failed to parse MT940 format. Error: {0}").format(str(e)))

    if not transactions:
        frappe.throw(_("Parsed file is not in valid MT940 format or contains no transactions."))

    csv_buffer = io.StringIO()
    writer = csv.writer(csv_buffer)

    # 包含付款凭证所需字段的CSV表头
    headers = [
        "Date", "Deposit", "Withdrawal", "Description", 
        "Reference Number", "Bank Account", "Currency",
        "Supplier", "Supplier Reference", "Invoice Number",
        "Bank Account Number", "IBAN", "Transaction Type"  # 添加交易类型字段
    ]
    writer.writerow(headers)

    for txn in transactions:
        txn_date = getattr(txn, "date", None)
        raw_date = txn.data.get("date", "")

        if txn_date:
            date_str = txn_date.strftime("%Y-%m-%d")
        elif isinstance(raw_date, date | datetime):
            date_str = raw_date.strftime("%Y-%m-%d")
        else:
            date_str = str(raw_date)

        raw_amount = str(txn.data.get("amount", ""))
        parts = raw_amount.strip().split()
        amount_value = float(parts[0]) if parts else 0.0

        deposit = amount_value if amount_value > 0 else ""
        withdrawal = abs(amount_value) if amount_value < 0 else ""
        description = txn.data.get("extra_details") or ""
        reference = txn.data.get("transaction_reference") or ""
        currency = txn.data.get("currency", "")
        
        # 提取银行账号和IBAN信息（如果有）
        bank_account_number = txn.data.get("bank_account_number", "")
        iban = txn.data.get("iban", "")
        
        # 确定交易类型
        transaction_type = "Inward" if amount_value > 0 else "Outward"
        
        # 提取供应商相关信息
        supplier = ""
        supplier_ref = ""
        invoice_no = ""
        if description:
            desc_parts = description.split("|")
            if len(desc_parts) >= 2:
                supplier = desc_parts[0].strip()
                supplier_ref = desc_parts[1].strip()
                invoice_no = desc_parts[2].strip() if len(desc_parts) > 2 else ""

        writer.writerow([
            date_str, deposit, withdrawal, description, reference, 
            doc.bank_account, currency, supplier, supplier_ref, invoice_no,
            bank_account_number, iban, transaction_type
        ])

    csv_content = csv_buffer.getvalue().encode("utf-8")
    csv_buffer.close()

    filename = f"{frappe.utils.now_datetime().strftime('%Y%m%d%H%M%S')}_converted_mt940.csv"
    saved_file = save_file(filename, csv_content, doc.doctype, doc.name, is_private=True, df="import_file")

    return saved_file.file_url


@frappe.whitelist()
def get_preview_from_template(data_import, import_file=None, google_sheets_url=None):
    return frappe.get_doc("Bank Statement Import", data_import).get_preview_from_template(
        import_file, google_sheets_url
    )


@frappe.whitelist()
def form_start_import(data_import):
    job_id = frappe.get_doc("Bank Statement Import", data_import).start_import()
    return job_id is not None


@frappe.whitelist()
def download_errored_template(data_import_name):
    data_import = frappe.get_doc("Bank Statement Import", data_import_name)
    data_import.export_errored_rows()


@frappe.whitelist()
def download_import_log(data_import_name):
    return frappe.get_doc("Bank Statement Import", data_import_name).download_import_log()


def is_mt940_format(content: str) -> bool:
    required_tags = [":20:", ":25:", ":28C:", ":61:"]
    return all(tag in content for tag in required_tags)


def parse_data_from_template(raw_data):
    data = []
    for _i, row in enumerate(raw_data):
        if all(v in INVALID_VALUES for v in row):
            continue
        data.append(row)
    return data


def start_import(data_import, bank_account, import_file_path, google_sheets_url, bank, template_options, 
                auto_create_payment_entry=True, payment_submit_option="Save as Draft"):
    """后台执行导入并生成付款凭证"""
    update_mapping_db(bank, template_options)

    data_import = frappe.get_doc("Bank Statement Import", data_import)
    file = import_file_path if import_file_path else google_sheets_url

    import_file = ImportFile("Bank Transaction", file=file, import_type="Insert New Records")
    data = parse_data_from_template(import_file.raw_data)

    if not data_import.get("payload_count"):
        data_import.payload_count = len(data) - 1

    if import_file_path:
        add_bank_account(data, bank_account)
        write_files(import_file, data)

    try:
        i = Importer(data_import.reference_doctype, data_import=data_import)
        import_result = i.import_data()
        
        # 生成付款凭证
        if auto_create_payment_entry and import_result:
            new_transactions = frappe.get_all(
                "Bank Transaction",
                filters={"bank_account": bank_account, "creation": [">=", data_import.modified]},
                order_by="creation desc",
                limit=data_import.payload_count
            )
            
            if new_transactions:
                transactions = [frappe.get_doc("Bank Transaction", t.name) for t in new_transactions]
                data_import.auto_create_payment_entry = auto_create_payment_entry
                data_import.payment_submit_option = payment_submit_option
                data_import.create_payment_entries(transactions)
                
    except Exception:
        frappe.db.rollback()
        data_import.db_set("status", "Error")
        data_import.log_error("Bank Statement Import failed")
    finally:
        frappe.flags.in_import = False

    frappe.publish_realtime("data_import_refresh", {"data_import": data_import.name})


def update_mapping_db(bank, template_options):
    bank = frappe.get_doc("Bank", bank)
    for d in bank.bank_transaction_mapping:
        d.delete()

    for d in json.loads(template_options)["column_to_field_map"].items():
        bank.append("bank_transaction_mapping", {"bank_transaction_field": d[1], "file_field": d[0]})

    bank.save()


def add_bank_account(data, bank_account):
    bank_account_loc = None
    if "Bank Account" not in data[0]:
        data[0].append("Bank Account")
    else:
        for loc, header in enumerate(data[0]):
            if header == "Bank Account":
                bank_account_loc = loc

    for row in data[1:]:
        if bank_account_loc:
            row[bank_account_loc] = bank_account
        else:
            row.append(bank_account)


def write_files(import_file, data):
    full_file_path = import_file.file_doc.get_full_path()
    parts = import_file.file_doc.get_extension()
    extension = parts[1].lstrip(".")

    if extension == "csv":
        with open(full_file_path, "w", newline="") as file:
            writer = csv.writer(file)
            writer.writerows(data)
    elif extension in ["xlsx", "xls"]:
        write_xlsx(data, "trans", file_path=full_file_path)


def write_xlsx(data, sheet_name, wb=None, column_widths=None, file_path=None):
    column_widths = column_widths or []
    if wb is None:
        wb = openpyxl.Workbook(write_only=True)

    ws = wb.create_sheet(sheet_name, 0)

    for i, column_width in enumerate(column_widths):
        if column_width:
            ws.column_dimensions[get_column_letter(i + 1)].width = column_width

    row1 = ws.row_dimensions[1]
    row1.font = Font(name="Calibri", bold=True)

    for row in data:
        clean_row = []
        for item in row:
            if isinstance(item, str) and (sheet_name not in ["Data Import Template", "Data Export"]):
                value = handle_html(item)
            else:
                value = item

            if isinstance(item, str) and next(ILLEGAL_CHARACTERS_RE.finditer(value), None):
                value = re.sub(ILLEGAL_CHARACTERS_RE, "", value)

            clean_row.append(value)

        ws.append(clean_row)

    wb.save(file_path)
    return True


@frappe.whitelist()
def get_import_status(docname):
    import_status = {"status": frappe.db.get_value("Bank Statement Import", docname, "status")}

    logs = frappe.get_all(
        "Data Import Log",
        fields=["count(*) as count", "success"],
        filters={"data_import": docname},
        group_by="success",
    )

    total_payload_count = 0
    for log in logs:
        total_payload_count += log.get("count", 0)
        if log.get("success"):
            import_status["success"] = log.get("count")
        else:
            import_status["failed"] = log.get("count")

    import_status["total_records"] = total_payload_count
    return import_status


@frappe.whitelist()
def get_payment_entry_status(data_import_name):
    """获取付款凭证创建状态"""
    success = frappe.db.count("Payment Entry", {
        "bank_statement_import": data_import_name,
        "status": ["in", ["Draft", "Submitted"]]
    })
    
    failed = frappe.db.count("Error Log", {
        "method": ["like", f"%{data_import_name}%"],
        "error": ["like", "%Payment Entry Creation Failed%"]
    })
    
    total = success + failed
    return {"success": success, "failed": failed, "total": total}


@frappe.whitelist()
def get_payment_entry_errors(data_import_name):
    """获取付款凭证创建错误"""
    errors = []
    logs = frappe.get_all("Error Log", {
        "method": ["like", f"%{data_import_name}%"],
        "error": ["like", "%Payment Entry Creation Failed%"]
    }, ["error", "creation"], order_by="creation desc")
    
    for log in logs:
        # 解析错误信息提取交易信息
        trans_match = re.search(r"transaction (.*?) \(Description", log.error)
        if trans_match:
            errors.append({
                "transaction": trans_match.group(1),
                "message": re.sub(r"^.*?: ", "", log.error)
            })
    return errors


@frappe.whitelist()
def get_import_logs(docname: str):
    frappe.has_permission("Bank Statement Import", throw=True)

    return frappe.get_all(
        "Data Import Log",
        fields=["success", "docname", "messages", "exception", "row_indexes"],
        filters={"data_import": docname},
        limit_page_length=5000,
        order_by="log_index",
    )


@frappe.whitelist()
def upload_bank_statement(**args):
    args = frappe._dict(args)
    bsi = frappe.new_doc("Bank Statement Import")

    if args.company:
        bsi.update({"company": args.company})

    if args.bank_account:
        bsi.update({"bank_account": args.bank_account})

    return bsi
@frappe.whitelist()
def create_payment_entry_bts(
	bank_transaction_name,
	reference_number=None,
	reference_date=None,
	party_type=None,
	party=None,
	posting_date=None,
	mode_of_payment=None,
	project=None,
	cost_center=None,
	allow_edit=None,
):
	# Create a new payment entry based on the bank transaction
	bank_transaction = frappe.db.get_values(
		"Bank Transaction",
		bank_transaction_name,
		fieldname=["name", "unallocated_amount", "deposit", "bank_account", "currency"],
		as_dict=True,
	)[0]

	payment_type = "Receive" if bank_transaction.deposit > 0.0 else "Pay"

	bank_account = frappe.get_cached_value("Bank Account", bank_transaction.bank_account, "account")
	company = frappe.get_cached_value("Account", bank_account, "company")
	party_account = get_party_account(party_type, party, company)

	bank_currency = bank_transaction.currency
	party_currency = frappe.get_cached_value("Account", party_account, "account_currency")

	exc_rate = get_exchange_rate(bank_currency, party_currency, posting_date)

	amt_in_bank_acc_currency = bank_transaction.unallocated_amount
	amount_in_party_currency = bank_transaction.unallocated_amount * exc_rate

	pe = frappe.new_doc("Payment Entry")
	pe.payment_type = payment_type
	pe.company = company
	pe.reference_no = reference_number
	pe.reference_date = reference_date
	pe.party_type = party_type
	pe.party = party
	pe.posting_date = posting_date
	pe.paid_from = party_account if payment_type == "Receive" else bank_account
	pe.paid_to = party_account if payment_type == "Pay" else bank_account
	pe.paid_from_account_currency = party_currency if payment_type == "Receive" else bank_currency
	pe.paid_to_account_currency = party_currency if payment_type == "Pay" else bank_currency
	pe.paid_amount = amount_in_party_currency if payment_type == "Receive" else amt_in_bank_acc_currency
	pe.received_amount = amount_in_party_currency if payment_type == "Pay" else amt_in_bank_acc_currency
	pe.mode_of_payment = mode_of_payment
	pe.project = project
	pe.cost_center = cost_center

	pe.validate()

	if allow_edit:
		return pe

	pe.insert()
	pe.submit()

	vouchers = json.dumps(
		[
			{
				"payment_doctype": "Payment Entry",
				"payment_name": pe.name,
				"amount": amt_in_bank_acc_currency,
			}
		]
	)
	return reconcile_vouchers(bank_transaction_name, vouchers)

@frappe.whitelist()
def get_linked_payments(
	bank_transaction_name,
	document_types=None,
	from_date=None,
	to_date=None,
	filter_by_reference_date=None,
	from_reference_date=None,
	to_reference_date=None,
):
	# get all matching payments for a bank transaction
	transaction = frappe.get_doc("Bank Transaction", bank_transaction_name)
	bank_account = frappe.db.get_values(
		"Bank Account", transaction.bank_account, ["account", "company"], as_dict=True
	)[0]
	(gl_account, company) = (bank_account.account, bank_account.company)
	matching = check_matching(
		gl_account,
		company,
		transaction,
		document_types,
		from_date,
		to_date,
		filter_by_reference_date,
		from_reference_date,
		to_reference_date,
	)
	return subtract_allocations(gl_account, matching)

